﻿using System;
using System.Data;
using System.Data.DBLib;
using SysComm;

namespace SysOperationLayer.DBHandle
{
    public class ClientsDB : DBBase
    {
        public DataTable getClientsList(string client, string chnName, string name, string contactPerson, int isDelete)
        {
            if (ConnectDB())
            {
                string strSql = "select * from Client where 1=1";

                if (client != "" && client != null)
                {
                    strSql += " and Client like '%" + client + "%'";
                }
                if (chnName != "" && chnName != null)
                {
                    strSql += " and ChineseName like '%" + chnName + "%'";
                }
                if (name != "" && name != null)
                {
                    strSql += " and Name like '%" + name + "%'";
                }
                if (contactPerson != "" && contactPerson != null)
                {
                    strSql += " and ContactPerson like '%" + contactPerson + "%'";
                }

                strSql += " and IsDelete=" + isDelete;
                return m_objDB.getDataTable(strSql, "Client");
            }
            else
            {
                m_strMessage = "Cannot Connect to the Database!";
            }

            return null;
        }
        
        public DataRow GetItem(string strClient)
        {
            if (ConnectDB())
            {
                string strSql = "select * from Client where Client='";
                strSql += strClient;
                strSql += "'";
                return m_objDB.getDataRow(strSql, "Client");
            }
            else
            {
                m_strMessage = "Cannot Connect to the Database!";
            }

            return null;
        }

        public int Insert(string client, string name, string chineseName, string address,
                                string webStite, string jobTitle,
                                string contactPerson, string phone, string fax, string email,
                                string contactPerson2, string phone2, string fax2, string email2,
                                string strUserID, string strUserIP)
        {
            if (ConnectDB())
            {
                CDBError objError = new CDBError();
                string strSql = "insert into Client(Client, Name, ChineseName, Address, WebSite, JobTitle, ContactPerson, Phone, Fax, EmailAddress ,ContactPerson2, Phone2, Fax2, EmailAddress2";
                strSql += ", CreateIp, CreateBy, CreateDate, UpdateIp, UpdateBy, UpdateDate)";
                strSql += " values('" + client + "','" + name + "','" + chineseName + "','" + address + "','" + webStite + "','" + jobTitle + "','" + contactPerson + "','" + phone + "','" + fax + "','" + email + "','" + contactPerson2 + "','" + phone2 + "','" + fax2 + "','" + email2 + "','";
                strSql += strUserIP + "','" + strUserID + "',getDate(),'" + strUserIP + "','" + strUserID + "',getDate())";
                m_objDB.updateData(strSql, objError);
                m_strMessage = objError.Message;
                return objError.RetCode;
            }
            else
            {
                m_strMessage = "Cannot Connect to the Database!";
            }

            return -1;
        }

        public int Update(string client, string name, string chineseName, string address,
                                string webStite, string jobTitle,
                                string contactPerson, string phone, string fax, string email,
                                string contactPerson2, string phone2, string fax2, string email2,
                                string strUserID, string strUserIP)
        {
            if (IsEmpty(client))
            {
                m_strMessage = "Client Code Is Empty!";
            }
            else if (ConnectDB())
            {
                string strSql = "update Client set ";
                strSql += " Name='" + name + "'";
                strSql += ", ChineseName='" + chineseName + "'";
                strSql += ", Address='" + address + "'";
                strSql += ", WebSite='" + webStite + "'";
                strSql += ", JobTitle='" + jobTitle + "'";
                strSql += ", ContactPerson='" + contactPerson + "'";
                strSql += ", Phone='" + phone + "'";
                strSql += ", Fax='" + fax + "'";
                strSql += ", EmailAddress='" + email + "'";
                strSql += ", ContactPerson2='" + contactPerson2 + "'";
                strSql += ", Phone2='" + phone2 + "'";
                strSql += ", Fax2='" + fax2 + "'";
                strSql += ", EmailAddress2='" + email2 + "'";
                strSql += ", UpdateBy='" + strUserID + "'";
                strSql += ", UpdateIp='" + strUserIP + "'";
                strSql += ", UpdateDate=getDate()";
                strSql += " where Client='" + client + "'";

                CDBError objError = new CDBError();
                m_objDB.updateData(strSql, objError);

                m_strMessage = objError.Message;
                return objError.RetCode;
            }
            else
            {
                m_strMessage = "Cannot Connect to the Database!";
            }

            return -1;
        }

        public int Delete(string strClient, string strUserID, string strUserIP)
        {
            if (ConnectDB())
            {
                CDBError objError = new CDBError();
                string strSql = "update Client set ";
                strSql += " isDelete=1";
                strSql += ", DeleteBy='" + strUserID + "'";
                strSql += ", DeleteIP='" + strUserIP + "'";
                strSql += ", DeleteDate=getDate()";
                strSql += " where Client='" + strClient + "'";
                m_objDB.updateData(strSql, objError);
                m_strMessage = objError.Message;
                return objError.RetCode;
            }

            return -1;
        }

        public int Trash(string strClient)
        {
            if (ConnectDB())
            {
                CDBError objError = new CDBError();
                string strSql = "delete from Client where Client='" + strClient + "'";
                m_objDB.updateData(strSql, objError);
                m_strMessage = objError.Message;
                return objError.RetCode;
            }
            return -1;
        }

        public int Restore(string strClient)
        {
            if (ConnectDB())
            {
                CDBError objError = new CDBError();
                string strSql = "update Client set isDelete=0 where Client='" + strClient + "'";
                m_objDB.updateData(strSql, objError);
                m_strMessage = objError.Message;
                return objError.RetCode;
            }

            return -1;
        }
    }
}
